﻿using System;
using System.Data.SqlClient;
using System.Xml.Linq;
using Wintellect.PowerCollections;

namespace Dating
{
    public class Program
    {
        private const string PeoplePath = @"..\..\people.xml";
        private const string DatesPath = @"..\..\dates.xml";
        private const string DatingDatabaseName = "DatingDatabase";

        public static void ExtractDatesToXml(
            OrderedMultiDictionary<string, string> dates, string xmlOutputPath)
        {
            XElement root = new XElement("dates");
            XDocument datesDoc = new XDocument(root);
            foreach (var date in dates)
            {
                foreach (var item in date.Value)
                {
                    XElement element = new XElement("date");
                    element.Add(new XElement("person", new XAttribute("name", date.Key)));
                    element.Add(new XElement("person", new XAttribute("name", item)));
                    root.Add(element);
                }
            }
            datesDoc.Save(xmlOutputPath);
        }

        public static void ExtractDatesToDatabase(
            OrderedMultiDictionary<string, string> dates, string databaseName)
        {
            SqlConnection dbConnection = new SqlConnection("Server=.\\SQLEXPRESS; " +
               "Database=" + databaseName + "; Integrated Security=true");
            dbConnection.Open();
            using (dbConnection)
            {
                SqlCommand dropTableCommand = new SqlCommand();
                dropTableCommand.Connection = dbConnection;
                dropTableCommand.CommandText =
                    "IF EXISTS(SELECT * FROM sys.tables where name='Dates') \n" +
                    "BEGIN \n" +
                        "DROP TABLE Dates \n" +
                    "END";
                dropTableCommand.ExecuteNonQuery();

                SqlCommand createTableCommand = new SqlCommand();
                createTableCommand.Connection = dbConnection;
                createTableCommand.CommandText =
                    "CREATE TABLE Dates ( " + 
                    "DateID int IDENTITY PRIMARY KEY, " +
                    "Name1 nvarchar(50) NOT NULL, " +
                    "Name2 nvarchar(50) NOT NULL)";
                createTableCommand.ExecuteNonQuery();

                SqlCommand insertIntoTableCommand = new SqlCommand();
                insertIntoTableCommand.Connection = dbConnection;
                insertIntoTableCommand.CommandText =
                    "INSERT INTO Dates (Name1, Name2) " + 
                    "VALUES (@name1, @name2)";
                insertIntoTableCommand.Parameters.Add("@name1", null);
                insertIntoTableCommand.Parameters.Add("@name2", null);
                foreach (var date in dates)
                {
                    foreach (var item in date.Value)
                    {
                        insertIntoTableCommand.Parameters["@name1"].Value = date.Key;
                        insertIntoTableCommand.Parameters["@name2"].Value = item;
                        insertIntoTableCommand.ExecuteNonQuery();
                    }
                }
            }
        }

        public static void Main()
        {
            DateTime start = DateTime.Now;
            Console.WriteLine("Start time: {0}", start);
            Console.WriteLine("Creating database.");
            DatabaseUtils.CreateDatabase(DatingDatabaseName);
            Console.WriteLine("Filling database.");
            DatabaseUtils.FillDatabase(PeoplePath, DatingDatabaseName);
            Console.WriteLine("Finding dates.");
            var allDates = DatabaseUtils.FindDates(DatingDatabaseName);
            Console.WriteLine("Extracting dates into xml.");
            ExtractDatesToXml(allDates, DatesPath);
            Console.WriteLine("Extracting dates into database.");
            ExtractDatesToDatabase(allDates, DatingDatabaseName);
            Console.WriteLine("Completed.");
            Console.WriteLine(DateTime.Now - start);
        }
    }
}
